//    uniCenta oPOS  - Touch Friendly Point Of Sale
//    Copyright (c) 2009-2013 uniCenta & previous Openbravo POS works
//    http://www.unicenta.com
//
//    This file is part of uniCenta oPOS
//
//    uniCenta oPOS is free software: you can redistribute it and/or modify
//    it under the terms of the GNU General Public License as published by
//    the Free Software Foundation, either version 3 of the License, or
//    (at your option) any later version.
//
//   uniCenta oPOS is distributed in the hope that it will be useful,
//    but WITHOUT ANY WARRANTY; without even the implied warranty of
//    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//    GNU General Public License for more details.
//
//    You should have received a copy of the GNU General Public License
//    along with uniCenta oPOS.  If not, see <http://www.gnu.org/licenses/>.

package com.openbravo.pos.migrate;

import com.openbravo.basic.BasicException;
import com.openbravo.data.gui.JMessageDialog;
import com.openbravo.data.gui.MessageInf;
import com.openbravo.data.loader.BatchSentence;
import com.openbravo.data.loader.BatchSentenceResource;
import com.openbravo.data.loader.Session;
import com.openbravo.data.user.DirtyManager;
import com.openbravo.pos.config.PanelConfig;
import com.openbravo.pos.forms.*;
import com.openbravo.pos.util.AltEncrypter;
import com.openbravo.pos.util.DirectoryEvent;
import java.awt.HeadlessException;
import java.io.File;
import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.regex.Matcher;
import javax.swing.*;


public class JPaneldbMigrate extends JPanel implements JPanelView {
        
    private DirtyManager dirty = new DirtyManager();

    private AppConfig config;
    private Connection con;
    private String sdbmanager;   
    private Session session;
    private AppProperties m_props;
    
    private Connection con2;
    private String sdbmanager2;
    private Session session2;    
    private ResultSet rs;
    private Statement stmt;
    private Statement stmt2;    
    private String SQL;  
    private PreparedStatement pstmt;   
    private String ticketsnum;
    private String ticketsnumRefund;
    private String ticketsnumPayment;    
    private List<PanelConfig> m_panelconfig;
    private String eScript="";
    private String eScript1="";
    private String eScript2="";
    private String eScript3="";
    
    
    /** Creates new form JPaneldbMigrate */
    public JPaneldbMigrate(AppView oApp) {
        this(oApp.getProperties());       
    }
    
    
    public JPaneldbMigrate(AppProperties props) {
        
        initComponents();
        jPanel2.setPreferredSize(new java.awt.Dimension(645, 209));
        config = new AppConfig(props.getConfigFile());      
        m_props=props;
        m_panelconfig = new ArrayList<>();
        config.load();
        for (PanelConfig c: m_panelconfig) {
            c.loadProperties(config);
        }
        
        
        jtxtDbDriverLib.getDocument().addDocumentListener(dirty);
        jtxtDbDriver.getDocument().addDocumentListener(dirty);
        jtxtDbURL.getDocument().addDocumentListener(dirty);
        jtxtDbPassword.getDocument().addDocumentListener(dirty);
        jtxtDbUser.getDocument().addDocumentListener(dirty);
        jbtnDbDriverLib.addActionListener(new DirectoryEvent(jtxtDbDriverLib));
              
        
    }
    
    
    @SuppressWarnings("empty-statement")
   public Boolean createMigratedb(){
       
            if ((!"MySQL".equals(sdbmanager2)) && (!"PostgreSQL".equals(sdbmanager2))){
                return (false);
            }
  
              eScript = "/com/openbravo/pos/scripts/" + sdbmanager2 + "-create.sql" ;
              eScript1 ="/com/openbravo/pos/scripts/" + sdbmanager2 +  "-createjl.sql";
              eScript2 ="/com/openbravo/pos/scripts/" + sdbmanager2 + "-DropFK.sql";
              eScript3 ="/com/openbravo/pos/scripts/SQL-CreateFK.sql";  

       if ("".equals(eScript)) {
           return (false);
       }
                // create a blank database to migrate into
                    try {
                        BatchSentence bsentence = new BatchSentenceResource(session2,  eScript);;                        
                        bsentence.putParameter("APP_ID", Matcher.quoteReplacement(AppLocal.APP_ID));
                        bsentence.putParameter("APP_NAME", Matcher.quoteReplacement(AppLocal.APP_NAME));                   
                        bsentence.putParameter("APP_VERSION", Matcher.quoteReplacement(AppLocal.APP_VERSION));
                       
                     
                        java.util.List l = bsentence.list();
                        if (l.size() > 0) {
                            JMessageDialog.showMessage(this, new MessageInf(MessageInf.SGN_WARNING, AppLocal.getIntString("migration.warning"), l.toArray(new Throwable[l.size()])));
                        }

                   } catch (BasicException e) {
                        JMessageDialog.showMessage(this, new MessageInf(MessageInf.SGN_DANGER, AppLocal.getIntString("migration.warningnodefault"), e));
                        session.close();
                    } finally{

                    }   
                    
// added jdl 21.04.13 changes updater 
// JG 2 Sept 13 Thank you John - now incorporated into 3.50
//                    
//                    try {
//                        BatchSentence bsentence = new BatchSentenceResource(session2,  eScript1);
//                        bsentence.putParameter("APP_ID", Matcher.quoteReplacement(AppLocal.APP_IDJL));
//                        bsentence.putParameter("APP_NAME", Matcher.quoteReplacement(AppLocal.APP_NAME));
//                        bsentence.putParameter("APP_VERSION", Matcher.quoteReplacement(AppLocal.APP_VERSIONJL));                      
//                        java.util.List l = bsentence.list();
//                        if (l.size() > 0) {
//                            JMessageDialog.showMessage(this, new MessageInf(MessageInf.SGN_WARNING, AppLocal.getIntString("migration.warning"), l.toArray(new Throwable[l.size()])));
//                        }
//
//                   } catch (BasicException e) {
//                        JMessageDialog.showMessage(this, new MessageInf(MessageInf.SGN_DANGER, AppLocal.getIntString("migration.warningnojl"), e));
//                        session.close();
//                    } finally{
//
//                    }   
                    
                    try {
                        BatchSentence bsentence = new BatchSentenceResource(session2,  eScript2);                     
                        java.util.List l = bsentence.list();
                        if (l.size() > 0) {
                            JMessageDialog.showMessage(this, new MessageInf(MessageInf.SGN_WARNING, AppLocal.getIntString("migration.warning"), l.toArray(new Throwable[l.size()])));
                        }

                   } catch (BasicException e) {
                        JMessageDialog.showMessage(this, new MessageInf(MessageInf.SGN_DANGER, AppLocal.getIntString("migration.warningnofk"), e));
                        session.close();
                    } finally{

                    }
                    
                    
            return (true);
   }
   
   public Boolean addFKeys(){
       if ("".equals(eScript3)) {
           return (false);
       }
                    try {
                        BatchSentence bsentence = new BatchSentenceResource(session2,  eScript3);                      
                        java.util.List l = bsentence.list();
                        if (l.size() > 0) {
                            JMessageDialog.showMessage(this, new MessageInf(MessageInf.SGN_WARNING, AppLocal.getIntString("migration.warning"), l.toArray(new Throwable[l.size()])));
                        }

                   } catch (BasicException e) {
                        JMessageDialog.showMessage(this, new MessageInf(MessageInf.SGN_DANGER, AppLocal.getIntString("database.ScriptNotFound"), e));
                        session.close();
                    } finally{

                    }   
            return (true);
   
   } 
   
   
    
    @Override
    public JComponent getComponent() {
        return this;
    }
    
    /**
     *
     * @return
     */
    @Override
    public String getTitle() {
        return AppLocal.getIntString("Menu.Configuration");
    } 
    
    public Boolean getSeconddbDetails(){

         String db_user2 =jtxtDbUser.getText();
         String db_url2 = jtxtDbURL.getText();
         char[] pass = jtxtDbPassword.getPassword();
         String db_password2 = new String(pass);
         
         Properties connectionProps = new Properties();
         connectionProps.put("user",db_user2);
         connectionProps.put("password",db_password2); 
         try {
         Class.forName(jtxtDbDriver.getText());

         ClassLoader cloader = new URLClassLoader(new URL[] {new File(jtxtDbDriverLib.getText()).toURI().toURL()});
         DriverManager.registerDriver(new DriverWrapper((Driver) Class.forName(jtxtDbDriver.getText(), true, cloader).newInstance()));
         con2 = (Connection) DriverManager.getConnection(db_url2,db_user2,db_password2);
         
         session2 = new Session(db_url2, db_user2,db_password2);
         sdbmanager2 = con2.getMetaData().getDatabaseProductName(); 
         return (true);
         } catch (ClassNotFoundException | MalformedURLException | InstantiationException | IllegalAccessException | SQLException e){
             JMessageDialog.showMessage(this, new MessageInf(MessageInf.SGN_DANGER, AppLocal.getIntString("database.UnableToConnect"), e));
         return (false);
         }
   

    }
    
    @Override
    public void activate() throws BasicException {
       // connect to the database
         String db_user =(m_props.getProperty("db.user"));
         String db_url = (m_props.getProperty("db.URL"));
         String db_password = (m_props.getProperty("db.password"));     
         
         if (db_user != null && db_password != null && db_password.startsWith("crypt:")) {
                // the password is encrypted
                AltEncrypter cypher = new AltEncrypter("cypherkey" + db_user);
                db_password = cypher.decrypt(db_password.substring(6));
         }
        
         try{
            session = AppViewConnection.createSession(m_props);
            con = DriverManager.getConnection(db_url,db_user,db_password); 
            sdbmanager = con.getMetaData().getDatabaseProductName();             
            } catch (BasicException | SQLException e) {    
// put some error trap here  
                
         JMessageDialog.showMessage(this, new MessageInf(MessageInf.SGN_DANGER, AppLocal.getIntString("database.UnableToConnect"), e));
         System.exit(0);
       }        
      
    }
    
    
    
    
    @Override
    public boolean deactivate() {
    return(true);
    }      

    
    
    
    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jbtnMigrate = new javax.swing.JButton();
        jbtnExit = new javax.swing.JButton();
        jPanel1 = new javax.swing.JPanel();
        jPanel2 = new javax.swing.JPanel();
        jLabel18 = new javax.swing.JLabel();
        jtxtDbDriverLib = new javax.swing.JTextField();
        jbtnDbDriverLib = new javax.swing.JButton();
        jLabel1 = new javax.swing.JLabel();
        jtxtDbDriver = new javax.swing.JTextField();
        jLabel2 = new javax.swing.JLabel();
        jtxtDbURL = new javax.swing.JTextField();
        jLabel3 = new javax.swing.JLabel();
        jtxtDbUser = new javax.swing.JTextField();
        jLabel4 = new javax.swing.JLabel();
        jtxtDbPassword = new javax.swing.JPasswordField();

        setPreferredSize(new java.awt.Dimension(600, 300));

        jbtnMigrate.setFont(new java.awt.Font("Arial", 0, 12)); // NOI18N
        jbtnMigrate.setText(AppLocal.getIntString("button.migrate")); // NOI18N
        jbtnMigrate.setMaximumSize(new java.awt.Dimension(70, 33));
        jbtnMigrate.setMinimumSize(new java.awt.Dimension(70, 33));
        jbtnMigrate.setPreferredSize(new java.awt.Dimension(70, 33));
        jbtnMigrate.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jbtnMigrateActionPerformed(evt);
            }
        });

        jbtnExit.setFont(new java.awt.Font("Arial", 0, 12)); // NOI18N
        jbtnExit.setText(AppLocal.getIntString("Button.Exit")); // NOI18N
        jbtnExit.setMaximumSize(new java.awt.Dimension(70, 33));
        jbtnExit.setMinimumSize(new java.awt.Dimension(70, 33));
        jbtnExit.setPreferredSize(new java.awt.Dimension(70, 33));
        jbtnExit.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jbtnExitActionPerformed(evt);
            }
        });

        jPanel1.setLayout(null);

        jPanel2.setBorder(javax.swing.BorderFactory.createTitledBorder(null, "New Database details", javax.swing.border.TitledBorder.DEFAULT_JUSTIFICATION, javax.swing.border.TitledBorder.DEFAULT_POSITION, new java.awt.Font("Tahoma", 1, 11))); // NOI18N
        jPanel2.setFont(new java.awt.Font("Arial", 0, 12)); // NOI18N

        jLabel18.setFont(new java.awt.Font("Arial", 0, 12)); // NOI18N
        jLabel18.setText(AppLocal.getIntString("label.dbdriverlib")); // NOI18N

        jtxtDbDriverLib.setFont(new java.awt.Font("Arial", 0, 12)); // NOI18N
        jtxtDbDriverLib.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jtxtDbDriverLibActionPerformed(evt);
            }
        });

        jbtnDbDriverLib.setIcon(new javax.swing.ImageIcon(getClass().getResource("/com/openbravo/images/fileopen.png"))); // NOI18N
        jbtnDbDriverLib.setMaximumSize(new java.awt.Dimension(64, 32));
        jbtnDbDriverLib.setMinimumSize(new java.awt.Dimension(64, 32));
        jbtnDbDriverLib.setPreferredSize(new java.awt.Dimension(64, 32));

        jLabel1.setFont(new java.awt.Font("Arial", 0, 12)); // NOI18N
        jLabel1.setText(AppLocal.getIntString("Label.DbDriver")); // NOI18N

        jtxtDbDriver.setFont(new java.awt.Font("Arial", 0, 12)); // NOI18N

        jLabel2.setFont(new java.awt.Font("Arial", 0, 12)); // NOI18N
        jLabel2.setText(AppLocal.getIntString("Label.DbURL")); // NOI18N

        jtxtDbURL.setFont(new java.awt.Font("Arial", 0, 12)); // NOI18N
        jtxtDbURL.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jtxtDbURLActionPerformed(evt);
            }
        });

        jLabel3.setFont(new java.awt.Font("Arial", 0, 12)); // NOI18N
        jLabel3.setText(AppLocal.getIntString("Label.DbUser")); // NOI18N

        jtxtDbUser.setFont(new java.awt.Font("Arial", 0, 12)); // NOI18N

        jLabel4.setFont(new java.awt.Font("Arial", 0, 12)); // NOI18N
        jLabel4.setText(AppLocal.getIntString("Label.DbPassword")); // NOI18N

        jtxtDbPassword.setFont(new java.awt.Font("Arial", 0, 12)); // NOI18N

        javax.swing.GroupLayout jPanel2Layout = new javax.swing.GroupLayout(jPanel2);
        jPanel2.setLayout(jPanel2Layout);
        jPanel2Layout.setHorizontalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel2Layout.createSequentialGroup()
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(jPanel2Layout.createSequentialGroup()
                        .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                            .addComponent(jLabel3, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(jLabel4, javax.swing.GroupLayout.DEFAULT_SIZE, 86, Short.MAX_VALUE))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(jtxtDbUser, javax.swing.GroupLayout.PREFERRED_SIZE, 177, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(jtxtDbPassword, javax.swing.GroupLayout.PREFERRED_SIZE, 177, javax.swing.GroupLayout.PREFERRED_SIZE)))
                    .addGroup(jPanel2Layout.createSequentialGroup()
                        .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                            .addComponent(jLabel18, javax.swing.GroupLayout.DEFAULT_SIZE, 86, Short.MAX_VALUE)
                            .addComponent(jLabel1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(jLabel2, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING, false)
                            .addComponent(jtxtDbDriver, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, 395, Short.MAX_VALUE)
                            .addComponent(jtxtDbDriverLib, javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(jtxtDbURL))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jbtnDbDriverLib, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );
        jPanel2Layout.setVerticalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel2Layout.createSequentialGroup()
                .addContainerGap(13, Short.MAX_VALUE)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                        .addComponent(jtxtDbDriverLib, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addComponent(jLabel18, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addComponent(jbtnDbDriverLib, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jtxtDbDriver, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel1, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jtxtDbURL, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel2, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jtxtDbUser, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel3, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jtxtDbPassword, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel4, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
        this.setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addComponent(jbtnMigrate, javax.swing.GroupLayout.PREFERRED_SIZE, 130, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(18, 18, 18)
                .addComponent(jbtnExit, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap())
            .addGroup(layout.createSequentialGroup()
                .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jPanel2, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(0, 6, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jPanel2, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jbtnMigrate, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jbtnExit, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap(46, Short.MAX_VALUE))
        );
    }// </editor-fold>//GEN-END:initComponents

    private void jbtnMigrateActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jbtnMigrateActionPerformed
      if (getSeconddbDetails()){
// check if this a supported migration path         
          
         if  (createMigratedb()){

        try {
        stmt = (Statement) con.createStatement();
        stmt2 = (Statement) con2.createStatement();
        
// copy attribute table       
        SQL ="SELECT * FROM ATTRIBUTE";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO ATTRIBUTE (ID, NAME) VALUES (?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("NAME")); 
            pstmt.executeUpdate();   
        }            
                           
//  copy attributeinstance table        
        SQL ="SELECT * FROM ATTRIBUTEINSTANCE"; 
        while (rs.next()){
            SQL="INSERT INTO ATTRIBUTEINSTANCE (ID, ATTRIBUTEINSTANCE_ID, ATTRIBUTE_ID, VALUE) VALUES (?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("ATTRIBUTEINSTANCE_ID")); 
            pstmt.setString(3,rs.getString("ATTRIBUTE_ID"));
            pstmt.setString(4,rs.getString("VALUE"));    
            pstmt.executeUpdate();              
        }        
        
// copy attributeset table       
        SQL ="SELECT * FROM ATTRIBUTESET";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO ATTRIBUTESET (ID, NAME) VALUES (?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("NAME")); 
            pstmt.executeUpdate();            
        }         
        
// copy attributesetinstance table       
        SQL ="SELECT * FROM ATTRIBUTESETINSTANCE";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO ATTRIBUTESETINSTANCE (ID, ATTRIBUTESET_ID, DESCRIPTION) VALUES (?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            //System.out.println(rs.getString("DESCRIPTION"));
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("ATTRIBUTESET_ID")); 
            pstmt.setString(3,rs.getString("DESCRIPTION"));             
            pstmt.executeUpdate();           
        } 
 
// copy attributeuse table       
        SQL ="SELECT * FROM ATTRIBUTEUSE";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO ATTRIBUTEUSE(ID, ATTRIBUTESET_ID, ATTRIBUTE_ID, LINENO) VALUES (?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("ATTRIBUTESET_ID")); 
            pstmt.setString(3,rs.getString("ATTRIBUTE_ID")); 
            pstmt.setInt(4,rs.getInt("LINENO"));             
            pstmt.executeUpdate();              
        }         
        
// copy attributevalue table       
        SQL ="SELECT * FROM ATTRIBUTEVALUE";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO ATTRIBUTEVALUE (ID, ATTRIBUTE_ID, VALUE) VALUES (?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("ATTRIBUTE_ID")); 
            pstmt.setString(3,rs.getString("VALUE"));             
            pstmt.executeUpdate(); 
        }         

// copy breaks table       
        SQL ="SELECT * FROM BREAKS";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO BREAKS(ID, NAME, NOTES, VISIBLE) VALUES (?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("NAME")); 
            pstmt.setString(3,rs.getString("NOTES")); 
            pstmt.setBoolean(4,rs.getBoolean("VISIBLE"));             
            pstmt.executeUpdate();              
        }        
        
// copy categories table       
        SQL ="SELECT * FROM CATEGORIES";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO CATEGORIES(ID, NAME, PARENTID, IMAGE, TEXTTIP, CATSHOWNAME ) VALUES (?, ?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("NAME")); 
            pstmt.setString(3,rs.getString("PARENTID"));
            pstmt.setBytes(4,rs.getBytes("IMAGE"));            
            pstmt.setString(5,rs.getString("TEXTTIP"));            
            pstmt.setBoolean(6,rs.getBoolean("CATSHOWNAME"));             
            pstmt.executeUpdate();              
        } 
        
// copy closedcash  table       
        SQL ="SELECT * FROM CLOSEDCASH";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO CLOSEDCASH(MONEY, HOST, HOSTSEQUENCE, DATESTART, DATEEND ) VALUES (?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("MONEY")); 
            pstmt.setString(2,rs.getString("HOST")); 
            pstmt.setInt(3,rs.getInt("HOSTSEQUENCE"));
            pstmt.setTimestamp(4,rs.getTimestamp("DATESTART"));            
            pstmt.setTimestamp(5,rs.getTimestamp("DATEEND"));                        
            pstmt.executeUpdate();              
        }         

        
        
// copy csvimport  table       
        SQL ="SELECT * FROM CSVIMPORT";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO CSVIMPORT (ID, ROWNUMBER, CSVERROR, REFERENCE, CODE, NAME, BUYPRICE, SELLPRICE, PREVIOUSBUY, PREVIOUSSELL  ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("ROWNUMBER")); 
            pstmt.setString(3,rs.getString("CSVERROR"));
            pstmt.setString(4,rs.getString("REFERENCE"));            
            pstmt.setString(5,rs.getString("CODE")); 
            pstmt.setString(6,rs.getString("NAME")); 
            pstmt.setDouble(7,rs.getDouble("BUYPRICE")); 
            pstmt.setDouble(8,rs.getDouble("SELLPRICE")); 
            pstmt.setDouble(9,rs.getDouble("PREVIOUSBUY")); 
            pstmt.setDouble(10,rs.getDouble("PREVIOUSSELL"));                         
            pstmt.executeUpdate();              
        }         
         
        
        
// copy CUSTOMERS  table       
        SQL ="SELECT * FROM CUSTOMERS";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO CUSTOMERS (ID, SEARCHKEY, TAXID, NAME, TAXCATEGORY, CARD, MAXDEBT, ADDRESS, ADDRESS2, POSTAL, CITY,  REGION, COUNTRY, FIRSTNAME, LASTNAME, EMAIL, PHONE, PHONE2, FAX, NOTES, VISIBLE, CURDATE, CURDEBT )"
                    + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("SEARCHKEY")); 
            pstmt.setString(3,rs.getString("TAXID"));
            pstmt.setString(4,rs.getString("NAME"));            
            pstmt.setString(5,rs.getString("TAXCATEGORY")); 
            pstmt.setString(6,rs.getString("CARD")); 
            pstmt.setDouble(7,rs.getDouble("MAXDEBT")); 
            pstmt.setString(8,rs.getString("ADDRESS")); 
            pstmt.setString(9,rs.getString("ADDRESS2")); 
            pstmt.setString(10,rs.getString("POSTAL"));  
            pstmt.setString(11,rs.getString("CITY")); 
            pstmt.setString(12,rs.getString("REGION")); 
            pstmt.setString(13,rs.getString("COUNTRY"));
            pstmt.setString(14,rs.getString("FIRSTNAME"));            
            pstmt.setString(15,rs.getString("LASTNAME")); 
            pstmt.setString(16,rs.getString("EMAIL")); 
            pstmt.setString(17,rs.getString("PHONE")); 
            pstmt.setString(18,rs.getString("PHONE2")); 
            pstmt.setString(19,rs.getString("FAX")); 
            pstmt.setString(20,rs.getString("NOTES"));   
            pstmt.setBoolean(21,rs.getBoolean("VISIBLE")); 
            pstmt.setTimestamp(22,rs.getTimestamp("CURDATE")); 
            pstmt.setDouble(23,rs.getDouble("CURDEBT"));             
            pstmt.executeUpdate();              
        }         
        
        
// copy FLOORS table       
        SQL ="SELECT * FROM FLOORS";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO FLOORS (ID, NAME, IMAGE) VALUES (?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("NAME")); 
            pstmt.setBytes(3,rs.getBytes("IMAGE"));             
            pstmt.executeUpdate(); 
        }         
        

        
// copy LEAVES table       
        SQL ="SELECT * FROM LEAVES";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO LEAVES (ID, PPLID, NAME, STARTDATE, ENDDATE, NOTES) VALUES (?, ?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("PPLID")); 
            pstmt.setString(3,rs.getString("NAME"));                 
            pstmt.setTimestamp(4,rs.getTimestamp("STARTDATE")); 
            pstmt.setTimestamp(5,rs.getTimestamp("ENDDATE")); 
            pstmt.setString(6,rs.getString("NOTES"));            
            pstmt.executeUpdate(); 
        }                

        
        
// copy LOCATIONS table       
        SQL ="SELECT * FROM LOCATIONS";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO LOCATIONS (ID, NAME, ADDRESS) VALUES (?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("NAME")); 
            pstmt.setString(3,rs.getString("ADDRESS"));             
            pstmt.executeUpdate(); 
        }         
        
        
// copy MOORERS TABLE     
        SQL ="SELECT * FROM MOORERS";     
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO MOORERS (VESSELNAME, SIZE, DAYS, POWER) VALUES (?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("VESSELNAME")); 
            pstmt.setInt(2,rs.getInt("SIZE")); 
            pstmt.setInt(3,rs.getInt("DAYS"));  
            pstmt.setBoolean(4,rs.getBoolean("POWER"));             
            pstmt.executeUpdate(); 
        }        
        

        
// copy payments table       
        SQL ="SELECT * FROM PAYMENTS";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO PAYMENTS (ID, RECEIPT, PAYMENT, TOTAL, TRANSID, NOTES, RETURNMSG) VALUES (?, ?, ?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("RECEIPT")); 
            pstmt.setString(3,rs.getString("PAYMENT"));                 
            pstmt.setDouble(4,rs.getDouble("TOTAL")); 
            pstmt.setString(5,rs.getString("TRANSID")); 
            pstmt.setString(6,rs.getString("NOTES"));
            pstmt.setBytes(7,rs.getBytes("RETURNMSG"));            
            pstmt.executeUpdate(); 
        }         
        
         
// copy PEOPLE table       
        SQL ="SELECT * FROM PEOPLE";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO PEOPLE (ID, NAME, APPPASSWORD, CARD, ROLE, VISIBLE, IMAGE) VALUES (?, ?, ?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("NAME")); 
            pstmt.setString(3,rs.getString("APPPASSWORD"));                 
            pstmt.setString(4,rs.getString("CARD")); 
            pstmt.setString(5,rs.getString("ROLE")); 
            pstmt.setBoolean(6,rs.getBoolean("VISIBLE"));
            pstmt.setBytes(7,rs.getBytes("IMAGE"));            
            pstmt.executeUpdate(); 
        }         
        
// copy Places table         
        SQL ="SELECT * FROM PLACES";    
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO PLACES (ID, NAME, X, Y, FLOOR, CUSTOMER, WAITER, TICKETID, TABLEMOVED) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("NAME"));        
            pstmt.setInt(3,rs.getInt("X"));     
            pstmt.setInt(4,rs.getInt("Y"));        
            pstmt.setString(5,rs.getString("FLOOR"));
            pstmt.setString(6,rs.getString("CUSTOMER"));
            pstmt.setString(7,rs.getString("WAITER")); 
            pstmt.setString(8,rs.getString("TICKETID"));        
            pstmt.setBoolean(9,rs.getBoolean("TABLEMOVED")); 
            pstmt.executeUpdate();           
        }

        
// copy Products  table       
        SQL ="SELECT * FROM PRODUCTS";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO PRODUCTS (ID, REFERENCE, CODE, CODETYPE, NAME, PRICEBUY, PRICESELL, CATEGORY, TAXCAT, ATTRIBUTESET_ID, STOCKCOST, STOCKVOLUME, IMAGE, ISCOM, ISSCALE, ISKITCHEN, PRINTKB, SENDSTATUS, ISSERVICE, DISPLAY, ATTRIBUTES, ISVPRICE, ISVERPATRIB, TEXTTIP, WARRANTY )"
                    + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("REFERENCE")); 
            pstmt.setString(3,rs.getString("CODE"));
            pstmt.setString(4,rs.getString("CODETYPE"));            
            pstmt.setString(5,rs.getString("NAME")); 
            pstmt.setDouble(6,rs.getDouble("PRICEBUY")); 
            pstmt.setDouble(7,rs.getDouble("PRICESELL")); 
            pstmt.setString(8,rs.getString("CATEGORY")); 
            pstmt.setString(9,rs.getString("TAXCAT")); 
            pstmt.setString(10,rs.getString("ATTRIBUTESET_ID"));  
            pstmt.setDouble(11,rs.getDouble("STOCKCOST")); 
            pstmt.setDouble(12,rs.getDouble("STOCKVOLUME")); 
            pstmt.setBytes(13,rs.getBytes("IMAGE"));
            pstmt.setBoolean(14,rs.getBoolean("ISCOM"));            
            pstmt.setBoolean(15,rs.getBoolean("ISSCALE")); 
            pstmt.setBoolean(16,rs.getBoolean("ISKITCHEN")); 
            pstmt.setBoolean(17,rs.getBoolean("PRINTKB")); 
            pstmt.setBoolean(18,rs.getBoolean("SENDSTATUS")); 
            pstmt.setBoolean(19,rs.getBoolean("ISSERVICE")); 
            pstmt.setString(20,rs.getString("DISPLAY"));   
            pstmt.setBytes(21,rs.getBytes("ATTRIBUTES")); 
            pstmt.setBoolean(22,rs.getBoolean("ISVPRICE")); 
            pstmt.setBoolean(23,rs.getBoolean("ISVERPATRIB"));  
            pstmt.setString(24,rs.getString("TEXTTIP"));   
            pstmt.setBoolean(25,rs.getBoolean("WARRANTY"));            
            pstmt.executeUpdate();              
        }         
        
        
// copy PRODUCTS_CAT table       
        SQL ="SELECT * FROM PRODUCTS_CAT";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO PRODUCTS_CAT(PRODUCT, CATORDER) VALUES (?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("PRODUCT")); 
            pstmt.setInt(2,rs.getInt("CATORDER"));             
            pstmt.executeUpdate();             
        }         
        
        
 // copy PRODUCTS_COM table       
        SQL ="SELECT * FROM PRODUCTS_COM";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO PRODUCTS_COM(ID, PRODUCT, PRODUCT2 ) VALUES (?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("PRODUCT")); 
            pstmt.setString(3,rs.getString("PRODUCT2"));           
            pstmt.executeUpdate();             
        }         
               
 // copy RECEIPTS table       
        SQL ="SELECT * FROM RECEIPTS";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO RECEIPTS(ID, MONEY, DATENEW, ATTRIBUTES, PERSON ) VALUES (?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("MONEY")); 
            pstmt.setTimestamp(3,rs.getTimestamp("DATENEW")); 
            pstmt.setBytes(4,rs.getBytes("ATTRIBUTES")); 
            pstmt.setString(5,rs.getString("PERSON"));             
            pstmt.executeUpdate();             
        }          
        
// copy reservation_customers table       
        SQL ="SELECT * FROM RESERVATION_CUSTOMERS";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO RESERVATION_CUSTOMERS(ID, CUSTOMER) VALUES (?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("CUSTOMER"));             
            pstmt.executeUpdate();             
        }          
        
 // copy reservationS table       
        SQL ="SELECT * FROM RESERVATIONS";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO RESERVATIONS(ID, CREATED, DATENEW, TITLE, CHAIRS, ISDONE, DESCRIPTION ) VALUES (?, ?, ?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setTimestamp(2,rs.getTimestamp("CREATED")); 
            pstmt.setTimestamp(3,rs.getTimestamp("DATENEW")); 
            pstmt.setString(4,rs.getString("TITLE")); 
            pstmt.setInt(5,rs.getInt("CHAIRS"));
            pstmt.setBoolean(6,rs.getBoolean("ISDONE")); 
            pstmt.setString(7,rs.getString("DESCRIPTION"));            
            pstmt.executeUpdate();             
        }          
                
        
// copy resources table       
        SQL ="SELECT * FROM RESOURCES";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO RESOURCES(ID, NAME, RESTYPE, CONTENT) VALUES (?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("NAME")); 
            pstmt.setInt(3,rs.getInt("RESTYPE")); 
            pstmt.setBytes(4,rs.getBytes("CONTENT"));             
            pstmt.executeUpdate();             
        }        
        
        
 // copy ROLES table       
        SQL ="SELECT * FROM ROLES";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO ROLES(ID, NAME, PERMISSIONS ) VALUES (?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("NAME")); 
            pstmt.setBytes(3,rs.getBytes("PERMISSIONS"));           
            pstmt.executeUpdate();             
        }         
                  
        
 // copy SHAREDTICKETS table       
        SQL ="SELECT * FROM SHAREDTICKETS";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO SHAREDTICKETS(ID, NAME, CONTENT, APPUSER, PICKUPID ) VALUES (?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("NAME")); 
            pstmt.setBytes(3,rs.getBytes("CONTENT"));
            pstmt.setBytes(4,rs.getBytes("APPUSER"));
            pstmt.setInt(5,rs.getInt("PICKUPID"));            
            pstmt.executeUpdate();             
        }         
                   
        
 // copy SHIFT_BREAKS table       
        SQL ="SELECT * FROM SHIFT_BREAKS";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO SHIFT_BREAKS(ID, SHIFTID, BREAKID, STARTTIME, ENDTIME ) VALUES (?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("SHIFTID")); 
            pstmt.setString(3,rs.getString("BREAKID")); 
            pstmt.setTimestamp(4,rs.getTimestamp("STARTTIME"));            
            pstmt.setTimestamp(5,rs.getTimestamp("ENDTIME"));            
            pstmt.executeUpdate();             
        }          
        
        
 // copy SHIFTS table       
        SQL ="SELECT * FROM SHIFTS";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO SHIFTS(ID, STARTSHIFT, ENDSHIFT, PPLID ) VALUES (?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setTimestamp(2,rs.getTimestamp("STARTSHIFT")); 
            pstmt.setTimestamp(3,rs.getTimestamp("ENDSHIFT")); 
            pstmt.setString(4,rs.getString("PPLID"));                       
            pstmt.executeUpdate();             
        }         
        
        
 // copy STOCKCURRENT table       
        SQL ="SELECT * FROM STOCKCURRENT";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO STOCKCURRENT(LOCATION, PRODUCT, ATTRIBUTESETINSTANCE_ID, UNITS ) VALUES (?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("LOCATION")); 
            pstmt.setString(2,rs.getString("PRODUCT")); 
            pstmt.setString(3,rs.getString("ATTRIBUTESETINSTANCE_ID")); 
            pstmt.setDouble(4,rs.getDouble("UNITS"));                       
            pstmt.executeUpdate();             
        }                 
        
        
 // copy STOCKDIARY table       
        SQL ="SELECT * FROM STOCKDIARY";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO STOCKDIARY(ID, DATENEW, REASON, LOCATION, PRODUCT, ATTRIBUTESETINSTANCE_ID, UNITS, PRICE, APPUSER ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setTimestamp(2,rs.getTimestamp("DATENEW")); 
            pstmt.setInt(3,rs.getInt("REASON")); 
            pstmt.setString(4,rs.getString("LOCATION"));    
            pstmt.setString(5,rs.getString("PRODUCT")); 
            pstmt.setString(6,rs.getString("ATTRIBUTESETINSTANCE_ID")); 
            pstmt.setDouble(7,rs.getDouble("UNITS")); 
            pstmt.setDouble(8,rs.getDouble("PRICE")); 
            pstmt.setString(9,rs.getString("APPUSER"));             
            pstmt.executeUpdate();             
        }           
                
        
 // copy STOCKLEVEL table       
        SQL ="SELECT * FROM STOCKLEVEL";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO STOCKLEVEL(ID, LOCATION, PRODUCT, STOCKSECURITY, STOCKMAXIMUM ) VALUES (?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("LOCATION")); 
            pstmt.setString(3,rs.getString("PRODUCT")); 
            pstmt.setDouble(4,rs.getDouble("STOCKSECURITY")); 
            pstmt.setDouble(5,rs.getDouble("STOCKMAXIMUM"));                       
            pstmt.executeUpdate();             
        }        
        
// copy TAXCATEGORIES table       
        SQL ="SELECT * FROM TAXCATEGORIES";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO TAXCATEGORIES (ID, NAME) VALUES (?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("NAME")); 
            pstmt.executeUpdate();   
        }       
        
        
// copy TAXCUSTCATEGORIES table       
        SQL ="SELECT * FROM TAXCUSTCATEGORIES";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO TAXCUSTCATEGORIES (ID, NAME) VALUES (?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("NAME")); 
            pstmt.executeUpdate();   
        }       
                
        
        
// copy TAXES table       
        SQL ="SELECT * FROM TAXES";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO TAXES (ID, NAME, CATEGORY, CUSTCATEGORY, PARENTID, RATE, RATECASCADE, RATEORDER ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("NAME")); 
            pstmt.setString(3,rs.getString("CATEGORY")); 
            pstmt.setString(4,rs.getString("CUSTCATEGORY"));
            pstmt.setString(5,rs.getString("PARENTID")); 
            pstmt.setDouble(6,rs.getDouble("RATE"));
            pstmt.setBoolean(7,rs.getBoolean("RATECASCADE")); 
            pstmt.setInt(8,rs.getInt("RATEORDER"));            
            pstmt.executeUpdate();   
        }         
        

// copy TAXLINES table       
        SQL ="SELECT * FROM TAXLINES";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO TAXLINES (ID, RECEIPT, TAXID, BASE, AMOUNT ) VALUES (?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("RECEIPT")); 
            pstmt.setString(3,rs.getString("TAXID")); 
            pstmt.setDouble(4,rs.getDouble("BASE"));
            pstmt.setDouble(5,rs.getDouble("AMOUNT"));           
            pstmt.executeUpdate();   
        }        

        
// copy THIRDPARTIES table       
        SQL ="SELECT * FROM THIRDPARTIES";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO THIRDPARTIES (ID, CIF, NAME, ADDRESS, CONTACTCOMM, CONTACTFACT, PAYRULE, FAXNUMBER, PHONENUMBER, MOBILENUMBER, EMAIL, WEBPAGE, NOTES  ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setString(2,rs.getString("CIF")); 
            pstmt.setString(3,rs.getString("NAME")); 
            pstmt.setString(4,rs.getString("ADDRESS"));
            pstmt.setString(5,rs.getString("CONTACTCOMM"));  
            pstmt.setString(6,rs.getString("CONTACTFACT")); 
            pstmt.setString(7,rs.getString("PAYRULE")); 
            pstmt.setString(8,rs.getString("FAXNUMBER"));
            pstmt.setString(9,rs.getString("PHONENUMBER"));                         
            pstmt.setString(10,rs.getString("MOBILENUMBER")); 
            pstmt.setString(11,rs.getString("EMAIL")); 
            pstmt.setString(12,rs.getString("WEBPAGE"));
            pstmt.setString(13,rs.getString("NOTES")); 
            pstmt.executeUpdate();   
        }          
        
// copy TICKETLINES table       
        SQL ="SELECT * FROM TICKETLINES";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO TICKETLINES (TICKET, LINE, PRODUCT, ATTRIBUTESETINSTANCE_ID, UNITS, PRICE, TAXID, ATTRIBUTES ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("TICKET")); 
            pstmt.setInt(2,rs.getInt("LINE")); 
            pstmt.setString(3,rs.getString("PRODUCT")); 
            pstmt.setString(4,rs.getString("ATTRIBUTESETINSTANCE_ID"));
            pstmt.setDouble(5,rs.getDouble("UNITS"));  
            pstmt.setDouble(6,rs.getDouble("PRICE")); 
            pstmt.setString(7,rs.getString("TAXID")); 
            pstmt.setBytes(8,rs.getBytes("ATTRIBUTES"));
            pstmt.executeUpdate();   
        }        
        
        
// copy TICKETS table       
        SQL ="SELECT * FROM TICKETS";         
        rs = stmt.executeQuery(SQL);
        while (rs.next()){
            SQL="INSERT INTO TICKETS (ID, TICKETTYPE, TICKETID, PERSON, CUSTOMER, STATUS ) VALUES (?, ?, ?, ?, ?, ?)";
            pstmt = con2.prepareStatement(SQL);    
            pstmt.setString(1,rs.getString("ID")); 
            pstmt.setInt(2,rs.getInt("TICKETTYPE")); 
            pstmt.setInt(3,rs.getInt("TICKETID")); 
            pstmt.setString(4,rs.getString("PERSON"));
            pstmt.setString(5,rs.getString("CUSTOMER"));  
            pstmt.setInt(6,rs.getInt("STATUS")); 
            pstmt.executeUpdate();   
        }          
              
        
// GET THE SEQUENCE NUMBERS
        if (("Apache Derby".equals(sdbmanager)) || ("MySQL".equals(sdbmanager))){
            SQL ="SELECT * FROM TICKETSNUM";         
            rs = stmt.executeQuery(SQL);
                while (rs.next()){
                      ticketsnum=rs.getString("ID");
                }
            SQL ="SELECT * FROM TICKETSNUM_PAYMENT";         
            rs = stmt.executeQuery(SQL);
                while (rs.next()){
                      ticketsnumPayment=rs.getString("ID");
                }                           
            SQL ="SELECT * FROM TICKETSNUM_REFUND";         
            rs = stmt.executeQuery(SQL);
                while (rs.next()){
                      ticketsnumRefund=rs.getString("ID");
        }              
        } else {
            SQL ="SELECT * FROM TICKETSNUM";         
            rs = stmt.executeQuery(SQL);
                while (rs.next()){
                      ticketsnum=rs.getString("LAST_VALUE");
                }
            SQL ="SELECT * FROM TICKETSNUM_PAYMENT";         
            rs = stmt.executeQuery(SQL);
                while (rs.next()){
                      ticketsnumPayment=rs.getString("LAST_VALUE");
                }                      
            SQL ="SELECT * FROM TICKETSNUM_REFUND";         
            rs = stmt.executeQuery(SQL);
                while (rs.next()){
                      ticketsnumRefund=rs.getString("LAST_VALUE");
                }              
        }
         
        
        
// WRITE SEQUENCE NUMBER
        if (("Apache Derby".equals(sdbmanager2)) || ("MySQL".equals(sdbmanager2))){         
            SQL = "UPDATE TICKETSNUM SET ID=" + ticketsnum;
            stmt2.executeUpdate(SQL);                
            SQL ="UPDATE TICKETSNUM_PAYMENT SET ID=" + ticketsnumPayment; 
            stmt2.executeUpdate(SQL);            
            SQL ="UPDATE TICKETSNUM_REFUND SET ID=" + ticketsnumRefund;         
            stmt2.executeUpdate(SQL);              
        }else {
            SQL ="ALTER SEQUENCE TICKETSNUM RESTART WITH " + ticketsnum;         
            stmt2.executeUpdate(SQL);        
            SQL ="ALTER SEQUENCE TICKETSNUM_PAYMENT RESTART WITH " + ticketsnumPayment;         
            stmt2.executeUpdate(SQL); 
            SQL ="ALTER SEQUENCE TICKETSNUM_REFUND RESTART WITH " + ticketsnumRefund;         
            stmt2.executeUpdate(SQL);              
        }         
 
// Add foreign keys back into the datbase
        addFKeys();
        
// Write new database settings to properties file
        
        
        config.setProperty("db.driverlib", jtxtDbDriverLib.getText());
        config.setProperty("db.driver", jtxtDbDriver.getText());
        config.setProperty("db.URL", jtxtDbURL.getText());
        config.setProperty("db.user", jtxtDbUser.getText());
        AltEncrypter cypher = new AltEncrypter("cypherkey" + jtxtDbUser.getText());       
        config.setProperty("db.password", "crypt:" + cypher.encrypt(new String(jtxtDbPassword.getPassword())));
        dirty.setDirty(false);
      
        
        for (PanelConfig c: m_panelconfig) {
            c.saveProperties(config);
        }
     
        try {
           config.save();
           JOptionPane.showMessageDialog(this, AppLocal.getIntString("message.restartchanges"), AppLocal.getIntString("message.title"), JOptionPane.INFORMATION_MESSAGE);
        } catch (IOException e) {
            JMessageDialog.showMessage(this, new MessageInf(MessageInf.SGN_WARNING, AppLocal.getIntString("message.cannotsaveconfig"), e));
        }
      
        
        JOptionPane.showMessageDialog(this,"Migration complete.");
        jbtnMigrate.setEnabled(false);
        
        
      }catch (SQLException | HeadlessException e){
          JMessageDialog.showMessage(this, new MessageInf(MessageInf.SGN_WARNING, SQL , e));
        }                
        } else {
            JFrame frame = new JFrame();
            JOptionPane.showMessageDialog(frame,AppLocal.getIntString("message.migratenotsupported"),AppLocal.getIntString("message.nigratemessage"),JOptionPane.WARNING_MESSAGE);
                
     }   
     }        
    }//GEN-LAST:event_jbtnMigrateActionPerformed

    private void jbtnExitActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jbtnExitActionPerformed
        deactivate();
        System.exit(0);
    }//GEN-LAST:event_jbtnExitActionPerformed

    private void jtxtDbURLActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jtxtDbURLActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_jtxtDbURLActionPerformed

    private void jtxtDbDriverLibActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jtxtDbDriverLibActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_jtxtDbDriverLibActionPerformed
    
    
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel18;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JButton jbtnDbDriverLib;
    private javax.swing.JButton jbtnExit;
    private javax.swing.JButton jbtnMigrate;
    private javax.swing.JTextField jtxtDbDriver;
    private javax.swing.JTextField jtxtDbDriverLib;
    private javax.swing.JPasswordField jtxtDbPassword;
    private javax.swing.JTextField jtxtDbURL;
    private javax.swing.JTextField jtxtDbUser;
    // End of variables declaration//GEN-END:variables
    
}
